package com.seek.quartz;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.seek.cache.CacheUtil;
import com.seek.service.InterService;
import com.seek.util.CommonUtil;
import com.seek.util.DateUtil;
import com.seek.util.JsonUtil;

/**
 * 每日运营数据汇总
 * @author lushuangteng
 *
 */
public class OperStatsCountQuartz {
	private static Logger logger = LoggerFactory.getLogger(OperStatsCountQuartz.class);
	@Resource
	private InterService interService;

	public void run(){
		try{
			countStatsDay();
			countStatsAge();
			countStatsCity();
			refreshRedis();
		}catch(Exception e){
			logger.error(e.getMessage());
		}
	}
	
	// 昨日运营数据汇总
	private void countStatsDay() throws Exception{
		// 先清除昨日的汇总数据
		String sql = "delete from t_count_oper_stats_day where rq=date_sub(curdate(),interval 1 day)";
		interService.executeUpdate(sql);
		StringBuffer sb = new StringBuffer("");
		sb.append("insert into t_count_oper_stats_day(rq,loan_num,loan_money,loan_num1,loan_money1,loan_num2,loan_money2,down_num,down_money,")
		  .append(" handle_fee,repay_num,repay_money,repay_num1,repay_money1,repay_money2,repay_bj,repay_lx,delay_num,delay_money,delay_latefee,")
		  .append(" bad_num,bad_money,bad_latefee,agent_money,agent_money1,reg_num,act_num,certify_num)")
		  .append(" select date_sub(curdate(),interval 1 day) rq,")
		  .append(" ifnull(sum(loan_num),0) loan_num,ifnull(sum(loan_money),0) loan_money,")
		  .append(" ifnull(sum(loan_num1),0) loan_num1,ifnull(sum(loan_money1),0) loan_money1,")
		  .append(" ifnull(sum(loan_num) - sum(loan_num1),0) loan_num2,ifnull(sum(loan_money) - sum(loan_money1),0) loan_money2,")
		  .append(" ifnull(sum(down_num),0) down_num,ifnull(sum(down_money),0) down_money,")
		  .append(" ifnull(sum(handle_fee),0) handle_fee,ifnull(sum(repay_num),0) repay_num,")
		  .append(" ifnull(sum(repay_money),0) repay_money,ifnull(sum(repay_num1),0) repay_num1,")
		  .append(" ifnull(sum(repay_money1),0) repay_money1,ifnull(sum(repay_money2),0) repay_money2,ifnull(sum(repay_bj),0) repay_bj,ifnull(sum(repay_lx),0) repay_lx,")
		  .append(" ifnull(sum(delay_num),0) delay_num,ifnull(sum(delay_money),0) delay_money,")
		  .append(" ifnull(sum(delay_latefee),0) delay_latefee,ifnull(sum(bad_num),0) bad_num,")
		  .append(" ifnull(sum(bad_money),0) bad_money,ifnull(sum(bad_latefee),0) bad_latefee,")
		  .append(" ifnull(sum(agent_money),0) agent_money,ifnull(sum(agent_money1),0) agent_money1,")
		  .append(" ifnull(sum(reg_num),0) reg_num,ifnull(sum(act_num),0) act_num,")
		  .append(" ifnull(sum(certify_num),0) certify_num from (")
		  .append(" select count(1) loan_num,sum(a.req_money) loan_money,")
		  .append(" sum(case when a.loan_type=1 then 1 else 0 end) loan_num1,")
		  .append(" sum(case when a.loan_type=1 then a.req_money else 0 end) loan_money1,")
		  .append(" 0 down_num,")
		  .append(" 0 down_money,")
		  .append(" 0 handle_fee,")
		  .append(" 0 repay_num,0 repay_money,0 repay_num1,0 repay_money1,0 repay_money2, 0 repay_bj,0 repay_lx,")
		  .append(" 0 delay_num,0 delay_money,0 delay_latefee,0 bad_num,0 bad_money,0 bad_latefee,")
		  .append(" 0 agent_money,0 agent_money1,0 reg_num,0 act_num,0 certify_num")
		  .append(" from t_loan a where a.req_date>date_sub(curdate(),interval 1 day) and a.req_date<curdate()")  
		  .append(" union all")
		  .append(" select 0,0,0,0,")
		  .append(" sum(case when a.loan_status in (3,5,6) and a.loan_type=1 then 1 else 0 end) down_num,")
		  .append(" sum(case when a.loan_status in (3,5,6) and a.loan_type=1 then a.down_money else 0 end) down_money,")
		  .append(" sum(case when a.loan_status in (3,5,6) and a.loan_type=1 then a.handle_fee else 0 end) handle_fee,")
		  .append(" 0 repay_num,0 repay_money,0 repay_num1,0 repay_money1,0 repay_money2, 0 repay_bj,0 repay_lx,")
		  .append(" 0 delay_num,0 delay_money,0 delay_latefee,0 bad_num,0 bad_money,0 bad_latefee,")
		  .append(" 0 agent_money,0 agent_money1,0 reg_num,0 act_num,0 certify_num")
		  .append(" from t_loan a where a.down_time>date_sub(curdate(),interval 1 day) and a.down_time<curdate()")
		  .append(" union all")
		  .append(" select 0,0,0,0,0,0,0,")
		  .append(" count(1) repay_num,sum(a.pay_money) repay_money,")
		  .append(" sum(case when a.pay_type=3 then 1 else 0 end) repay_num1,")
		  .append(" sum(case when a.pay_type=3 then a.pay_money else 0 end) repay_money1,")
		  .append(" sum(a.latefee) repay_money2,sum(case when a.type=2 then a.pay_money - a.latefee else 0 end) repay_bj,sum(case when a.type=1 then a.pay_money - a.latefee else 0 end) repay_lx,")
		  .append(" 0,0,0,0,0,0,0,0,0,0,0")
		  .append(" from t_loan_repayment a where a.pay_time>date_sub(curdate(),interval 1 day) and a.pay_time<curdate() and a.pay_status=1")
		  .append(" union all")
		  .append(" select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,count(1) delay_num,sum(a.req_money) delay_money,")
		  .append(" sum(a.late_fee-a.off_latefee) delay_latefee,0,0,0,0,0,0,0,0")
		  .append(" from t_loan a where a.loan_status in (3,6) and a.end_time<now() and a.end_time>date_sub(curdate(),interval 1 day)")
		  .append(" union all")
		  .append(" select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,count(1) bad_num,sum(a.req_money) bad_money,")
		  .append(" sum(a.late_fee-a.off_latefee) bad_latefee,0,0,0,0,0")
		  .append(" from t_loan a where a.loan_status=6 and a.bad_time>date_sub(curdate(),interval 1 day) and a.bad_time<curdate()")
		  .append(" union all")
		  .append(" select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,")
		  .append(" sum(case when a.money_type=2 then a.money else 0 end) agent_money,sum(case when a.money_type=1 then a.money else 0 end) agent_money1,0,0,0")
		  .append(" from t_company_agent_money a where a.create_time>date_sub(curdate(),interval 1 day) and a.create_time<curdate()")
		  .append(" union all")
		  .append(" select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,count(1) reg_num,0,0")
		  .append(" from t_customer a where a.reg_date>date_sub(curdate(),interval 1 day) and a.reg_date<curdate()")
		  .append(" union all")
		  .append(" select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,count(1) act_num,0")
		  .append(" from t_customer a where a.active_date>date_sub(curdate(),interval 1 day) and a.active_date<curdate()")
		  .append(" union all")
		  .append(" select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,count(1) certify_num")
		  .append(" from t_customer a where a.authed_date>date_sub(curdate(),interval 1 day) and a.authed_date<curdate()) a");
		interService.executeUpdate(sb.toString());
	}
	
	// 近60天确认为坏帐客户年龄分布图
	@SuppressWarnings("unchecked")
	private void countStatsAge() throws Exception{
		StringBuffer sb = new StringBuffer("");
		sb.append("select b.age, count(1) sl from t_loan a,t_customer b")
		  .append(" where a.customer_id=b.id and a.loan_status=6 and a.bad_time>=date_sub(curdate(),interval 60 day)")
		  .append(" group by b.age");
		List<Map<String,Object>> data = interService.findMapBySql(sb.toString());
		int zsl = 0;
		for (Map<String,Object> m : data){
			zsl += CommonUtil.getIntValue(m.get("sl"));
		}
		// 先清除昨日的数据
		String sql = "delete from t_count_oper_stats_age where rq=date_sub(curdate(),interval 1 day)";
		interService.executeUpdate(sql);
		sql = "insert into t_count_oper_stats_age(rq,age,percent) select date_sub(curdate(),interval 1 day),?,?";
		for (Map<String,Object> m : data){
			interService.executeUpdate(sql, new Object[]{m.get("age"),this.getBl(CommonUtil.getIntValue(m.get("sl")), zsl)});
		}
	}
	
	// 近60天确认为坏帐客户地区分布图
	@SuppressWarnings("unchecked")
	private void countStatsCity() throws Exception{
		StringBuffer sb = new StringBuffer("");
		sb.append("select b.reg_city, count(1) sl from t_loan a,t_customer_certify b")
		  .append(" where a.customer_id=b.customer_id and a.loan_status=6 and a.bad_time>=date_sub(curdate(),interval 60 day)")
		  .append(" group by b.reg_city");
		List<Map<String,Object>> data = interService.findMapBySql(sb.toString());
		int zsl = 0;
		for (Map<String,Object> m : data){
			zsl += CommonUtil.getIntValue(m.get("sl"));
		}
		String sql = "delete from t_count_oper_stats_city where rq=date_sub(curdate(),interval 1 day)";
		interService.executeUpdate(sql);
		sql = "insert into t_count_oper_stats_city(rq,city,percent) select date_sub(curdate(),interval 1 day),?,?";
		for (Map<String,Object> m : data){
			interService.executeUpdate(sql, new Object[]{m.get("reg_city"),this.getBl(CommonUtil.getIntValue(m.get("sl")), zsl)});
		}
	}
	
	private int getBl(int x, int total){
		if (total == 0)
			return 0;
		if (x >= total)
			return 100;
		double x1= x * 1.0;
	    double tempresult=x1/total;   
	    
	    BigDecimal b = new BigDecimal(tempresult);  
	    return (int)(b.setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue() * 100);  
	}
	
	@SuppressWarnings("unchecked")
	private void refreshRedis() throws Exception{
		// 近6个月每月客户新增注册/认证通过曲线图：
		String sql = "select DATE_FORMAT(rq,'%Y-%m') yf,sum(reg_num) regNum,sum(certify_num) verifiedNum from t_count_oper_stats_day where rq>=date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract( day from now())-1 day),interval 6 month) group by DATE_FORMAT(rq,'%Y-%m') order by DATE_FORMAT(rq,'%Y-%m') asc";
		List<Map<String,Object>> ds = interService.findMapBySql(sql);
		List<String> months = OperStatsCountQuartz.geSixtMonths();
		List<Map<String,Object>> cached = new ArrayList<Map<String,Object>>();
		for (String s : months){
			boolean exist = false;
			for (Map<String,Object> m : ds){
				String yf = m.get("yf") == null ? null : m.get("yf").toString();
				if (s.equals(yf)){
					exist = true;
					cached.add(m);
					break;
				}
			}
			if (!exist){
				Map<String,Object> m = new HashMap<String,Object>();
				m.put("yf", s);
				m.put("regNum",0);
				m.put("verifiedNum",0);
				cached.add(m);
			}
		}
		CacheUtil.putRedisData("OPER_STATS_USER_TREND", JsonUtil.toJson(cached));
		
		// 近7日贷款，逾期及货款类型变化数据
		StringBuffer sb = new StringBuffer("select date_format(rq,'%Y-%m-%d') rq,loan_num orderNum,loan_money orderMoney,delay_num overdueNum,loan_num1 newNum,loan_num2 secondNum");
		sb.append(" from t_count_oper_stats_day where rq>date_sub(curdate(),interval 7 day)");
		ds = interService.findMapBySql(sb.toString());
		cached = new ArrayList<Map<String,Object>>();
		List<String> days = OperStatsCountQuartz.geSixtDays();
		for (String s : days){
			boolean exist = false;
			for (Map<String,Object> m : ds){
				String rq = m.get("rq") == null ? null : m.get("rq").toString();
				if (s.equals(rq)){
					exist = true;
					cached.add(m);
					break;
				}
			}
			if (!exist){
				Map<String,Object> m = new HashMap<String,Object>();
				m.put("rq", s);
				m.put("orderNum",0);
				m.put("orderMoney",0);
				m.put("overdueNum",0);
				m.put("newNum",0);
				m.put("secondNum",0);
				cached.add(m);
			}
		}
		CacheUtil.putRedisData("OPER_STATS_LOAN_TREND", JsonUtil.toJson(cached));
		
		// 近60天确认为坏帐客户年龄分布图
		sb = new StringBuffer("select age,percent from t_count_oper_stats_age where rq>date_sub(curdate(),interval 1 day) order by percent desc");
		ds = interService.findMapBySql(sb.toString());
		CacheUtil.putRedisData("OPER_STATS_BAD_AGE", JsonUtil.toJson(ds));
		
		// 近60天确认为坏帐客户地区分布图
		sb = new StringBuffer("select city,percent from t_count_oper_stats_city where rq>date_sub(curdate(),interval 1 day) order by percent desc");
		ds = interService.findMapBySql(sb.toString());
		CacheUtil.putRedisData("OPER_STATS_BAD_CITY", JsonUtil.toJson(ds));
	}
	
	// 取得不含当月在内的近6个月月份
	public static List<String> geSixtMonths(){
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
		List<String> rqList = new ArrayList<>();
		String dateString = null;
		for (int i = 0; i < 6; i++) {
			dateString = sdf.format(cal.getTime());		 
			rqList.add(dateString.substring(0, 7));
			cal.add(Calendar.MONTH, -1);
		}
		Collections.reverse(rqList);
		return rqList;
	}
	
	public static List<String> geSixtDays(){
		Calendar cal = Calendar.getInstance();
		List<String> rqList = new ArrayList<>();
		for (int i = 0; i < 6; i++) {
			cal.add(Calendar.DAY_OF_YEAR, -1);	 
			rqList.add(DateUtil.dateFormat(cal.getTime(), DateUtil.DATE_FORMAT_YMD));
			System.out.println(DateUtil.dateFormat(cal.getTime(), DateUtil.DATE_FORMAT_YMD));
		}
		Collections.reverse(rqList);
		return rqList;
	}
}
